package com.supermarket.dao;

import com.supermarket.model.SalesOrder;
import com.supermarket.util.DatabaseUtil;

import java.math.BigDecimal;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;

/**
 * 销售数据访问对象
 * 负责销售相关的数据库操作
 */
public class SalesDAO {

    /**
     * 处理销售（调用存储过程）
     * 
     * @param cashierId     收银员ID
     * @param productId     商品ID
     * @param quantity      销售数量
     * @param paymentMethod 支付方式
     * @return 销售结果：[订单ID, 结果码, 结果消息]
     */
    public Object[] processSale(int cashierId, int productId, BigDecimal quantity, String paymentMethod) {
        String sql = "{CALL sp_ProcessSale(?, ?, ?, ?, ?, ?, ?)}";

        Connection conn = null;
        CallableStatement cs = null;

        try {
            conn = DatabaseUtil.getConnection();
            cs = conn.prepareCall(sql);

            // 设置输入参数
            cs.setInt(1, cashierId);
            cs.setInt(2, productId);
            cs.setBigDecimal(3, quantity);
            cs.setString(4, paymentMethod);

            // 注册输出参数
            cs.registerOutParameter(5, Types.INTEGER); // p_order_id
            cs.registerOutParameter(6, Types.INTEGER); // p_result_code
            cs.registerOutParameter(7, Types.VARCHAR); // p_result_msg

            // 执行存储过程
            cs.execute();

            // 获取输出参数
            int orderId = cs.getInt(5);
            int resultCode = cs.getInt(6);
            String resultMsg = cs.getString(7);

            return new Object[] { orderId, resultCode, resultMsg };

        } catch (SQLException e) {
            e.printStackTrace();
            return new Object[] { -1, -1, "系统错误：" + e.getMessage() };
        } finally {
            DatabaseUtil.close(conn, cs);
        }
    }

    /**
     * 获取销售统计（调用存储过程）
     * 
     * @param startDate 开始日期
     * @param endDate   结束日期
     * @param cashierId 收银员ID（null表示所有收银员）
     * @return 销售统计列表
     */
    public List<Object[]> getSalesStatistics(Date startDate, Date endDate, Integer cashierId) {
        List<Object[]> statistics = new ArrayList<>();
        String sql = "{CALL sp_GetSalesStatistics(?, ?, ?)}";

        Connection conn = null;
        CallableStatement cs = null;
        ResultSet rs = null;

        try {
            conn = DatabaseUtil.getConnection();
            cs = conn.prepareCall(sql);
            cs.setDate(1, startDate);
            cs.setDate(2, endDate);
            if (cashierId != null) {
                cs.setInt(3, cashierId);
            } else {
                cs.setNull(3, Types.INTEGER);
            }

            rs = cs.executeQuery();

            while (rs.next()) {
                Object[] stat = new Object[] {
                        rs.getInt("cashier_id"),
                        rs.getString("cashier_name"),
                        rs.getInt("order_count"),
                        rs.getBigDecimal("total_amount"),
                        rs.getBigDecimal("total_discount"),
                        rs.getBigDecimal("final_amount"),
                        rs.getBigDecimal("avg_order_amount")
                };
                statistics.add(stat);
            }

        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DatabaseUtil.close(conn, cs, rs);
        }

        return statistics;
    }

    /**
     * 获取销售订单列表
     * 
     * @param cashierId 收银员ID（null表示所有收银员）
     * @param startDate 开始日期
     * @param endDate   结束日期
     * @return 销售订单列表
     */
    public List<SalesOrder> getSalesOrders(Integer cashierId, Date startDate, Date endDate) {
        List<SalesOrder> orders = new ArrayList<>();
        StringBuilder sql = new StringBuilder(
                "SELECT so.id, so.order_no, so.cashier_id, u.real_name as cashier_name, " +
                        "       so.total_amount, so.discount_amount, so.final_amount, so.payment_method, " +
                        "       so.order_status, so.order_date, so.order_time, so.created_at, " +
                        "       COUNT(sd.id) as item_count " +
                        "FROM sales_orders so " +
                        "JOIN users u ON so.cashier_id = u.id " +
                        "LEFT JOIN sales_details sd ON so.id = sd.order_id " +
                        "WHERE 1=1");

        List<Object> params = new ArrayList<>();

        if (cashierId != null) {
            sql.append(" AND so.cashier_id = ?");
            params.add(cashierId);
        }

        if (startDate != null) {
            sql.append(" AND so.order_date >= ?");
            params.add(startDate);
        }

        if (endDate != null) {
            sql.append(" AND so.order_date <= ?");
            params.add(endDate);
        }

        sql.append(" GROUP BY so.id, so.order_no, so.cashier_id, u.real_name, so.total_amount, " +
                "          so.discount_amount, so.final_amount, so.payment_method, so.order_status, " +
                "          so.order_date, so.order_time, so.created_at " +
                " ORDER BY so.created_at DESC");

        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;

        try {
            conn = DatabaseUtil.getConnection();
            ps = conn.prepareStatement(sql.toString());

            for (int i = 0; i < params.size(); i++) {
                ps.setObject(i + 1, params.get(i));
            }

            rs = ps.executeQuery();

            while (rs.next()) {
                SalesOrder order = new SalesOrder();
                order.setId(rs.getInt("id"));
                order.setOrderNo(rs.getString("order_no"));
                order.setCashierId(rs.getInt("cashier_id"));
                order.setCashierName(rs.getString("cashier_name"));
                order.setTotalAmount(rs.getBigDecimal("total_amount"));
                order.setDiscountAmount(rs.getBigDecimal("discount_amount"));
                order.setFinalAmount(rs.getBigDecimal("final_amount"));
                order.setPaymentMethod(rs.getString("payment_method"));
                order.setOrderStatus(rs.getString("order_status"));
                order.setOrderDate(rs.getDate("order_date"));
                order.setOrderTime(rs.getTime("order_time"));
                order.setCreatedAt(rs.getTimestamp("created_at"));
                order.setItemCount(rs.getInt("item_count"));
                orders.add(order);
            }

        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DatabaseUtil.close(conn, ps, rs);
        }

        return orders;
    }

    /**
     * 根据订单ID获取订单详情
     * 
     * @param orderId 订单ID
     * @return 销售订单对象
     */
    public SalesOrder getSalesOrderById(int orderId) {
        String sql = "SELECT so.id, so.order_no, so.cashier_id, u.real_name as cashier_name, " +
                "       so.total_amount, so.discount_amount, so.final_amount, so.payment_method, " +
                "       so.order_status, so.order_date, so.order_time, so.created_at " +
                "FROM sales_orders so " +
                "JOIN users u ON so.cashier_id = u.id " +
                "WHERE so.id = ?";

        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;

        try {
            conn = DatabaseUtil.getConnection();
            ps = conn.prepareStatement(sql);
            ps.setInt(1, orderId);
            rs = ps.executeQuery();

            if (rs.next()) {
                SalesOrder order = new SalesOrder();
                order.setId(rs.getInt("id"));
                order.setOrderNo(rs.getString("order_no"));
                order.setCashierId(rs.getInt("cashier_id"));
                order.setCashierName(rs.getString("cashier_name"));
                order.setTotalAmount(rs.getBigDecimal("total_amount"));
                order.setDiscountAmount(rs.getBigDecimal("discount_amount"));
                order.setFinalAmount(rs.getBigDecimal("final_amount"));
                order.setPaymentMethod(rs.getString("payment_method"));
                order.setOrderStatus(rs.getString("order_status"));
                order.setOrderDate(rs.getDate("order_date"));
                order.setOrderTime(rs.getTime("order_time"));
                order.setCreatedAt(rs.getTimestamp("created_at"));
                return order;
            }

        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DatabaseUtil.close(conn, ps, rs);
        }

        return null;
    }

    /**
     * 创建新的销售订单
     * 
     * @param order 销售订单对象
     * @return 新创建的订单ID，失败返回-1
     */
    public int createSalesOrder(SalesOrder order) {
        String sql = "INSERT INTO sales_orders (order_no, cashier_id, total_amount, discount_amount, " +
                "                        final_amount, payment_method, order_status, order_date, order_time) " +
                "VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)";

        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;

        try {
            conn = DatabaseUtil.getConnection();
            ps = conn.prepareStatement(sql, PreparedStatement.RETURN_GENERATED_KEYS);
            ps.setString(1, order.getOrderNo());
            ps.setInt(2, order.getCashierId());
            ps.setBigDecimal(3, order.getTotalAmount());
            ps.setBigDecimal(4, order.getDiscountAmount());
            ps.setBigDecimal(5, order.getFinalAmount());
            ps.setString(6, order.getPaymentMethod());
            ps.setString(7, order.getOrderStatus());
            ps.setDate(8, order.getOrderDate());
            ps.setTime(9, order.getOrderTime());

            int rows = ps.executeUpdate();
            if (rows > 0) {
                rs = ps.getGeneratedKeys();
                if (rs.next()) {
                    return rs.getInt(1);
                }
            }

        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DatabaseUtil.close(conn, ps, rs);
        }

        return -1;
    }

    /**
     * 创建完整的销售订单（包含订单详情和库存更新）
     * 
     * @param order 包含所有明细的销售订单对象
     * @return 成功创建并带有数据库生成ID和订单号的订单对象，失败返回null
     */
    public SalesOrder createCompleteSalesOrder(SalesOrder order) {
        Connection conn = null;
        String orderSql = "INSERT INTO sales_orders (cashier_id, total_amount, discount_amount, " +
                "final_amount, payment_method, order_status, order_date, order_time) " +
                "VALUES (?, ?, ?, ?, ?, ?, ?, ?)";
        String detailSql = "INSERT INTO sales_details (order_id, product_id, quantity, unit_price, promotion_id, discount_amount, subtotal) "
                +
                "VALUES (?, ?, ?, ?, ?, ?, ?)";
        String inventorySql = "UPDATE inventory SET current_stock = current_stock - ?, last_sale_date = CURDATE() WHERE product_id = ? AND current_stock >= ?";

        try {
            conn = DatabaseUtil.getConnection();
            conn.setAutoCommit(false); // 开启事务

            // 1. 插入主订单
            try (PreparedStatement psOrder = conn.prepareStatement(orderSql, PreparedStatement.RETURN_GENERATED_KEYS)) {
                psOrder.setInt(1, order.getCashierId());
                psOrder.setBigDecimal(2, order.getTotalAmount());
                psOrder.setBigDecimal(3, order.getDiscountAmount());
                psOrder.setBigDecimal(4, order.getFinalAmount());
                psOrder.setString(5, order.getPaymentMethod());
                psOrder.setString(6, order.getOrderStatus());
                psOrder.setDate(7, order.getOrderDate());
                psOrder.setTime(8, order.getOrderTime());

                if (psOrder.executeUpdate() > 0) {
                    try (ResultSet rs = psOrder.getGeneratedKeys()) {
                        if (rs.next()) {
                            order.setId(rs.getInt(1));
                        }
                    }
                } else {
                    throw new SQLException("创建订单失败，没有行受到影响。");
                }
            }

            // 2. 批量插入订单明细和更新库存
            try (PreparedStatement psDetail = conn.prepareStatement(detailSql);
                    PreparedStatement psInventory = conn.prepareStatement(inventorySql)) {

                for (com.supermarket.model.SalesDetail detail : order.getSalesDetails()) {
                    // 更新库存
                    psInventory.setBigDecimal(1, detail.getQuantity());
                    psInventory.setInt(2, detail.getProductId());
                    psInventory.setBigDecimal(3, detail.getQuantity());
                    if (psInventory.executeUpdate() == 0) {
                        throw new SQLException("库存不足，商品ID: " + detail.getProductId());
                    }

                    // 准备批量插入销售明细
                    detail.setOrderId(order.getId());
                    psDetail.setInt(1, detail.getOrderId());
                    psDetail.setInt(2, detail.getProductId());
                    psDetail.setBigDecimal(3, detail.getQuantity());
                    psDetail.setBigDecimal(4, detail.getUnitPrice());
                    psDetail.setObject(5, detail.getPromotionId());
                    psDetail.setBigDecimal(6, detail.getDiscountAmount());
                    psDetail.setBigDecimal(7, detail.getSubtotal());
                    psDetail.addBatch();
                }
                psDetail.executeBatch();
            }

            conn.commit(); // 提交事务

            // 3. 获取数据库生成的订单号并更新到订单对象
            try (PreparedStatement ps = conn.prepareStatement("SELECT order_no FROM sales_orders WHERE id = ?")) {
                ps.setInt(1, order.getId());
                try (ResultSet rs = ps.executeQuery()) {
                    if (rs.next()) {
                        order.setOrderNo(rs.getString("order_no"));
                    }
                }
            }

            return order;

        } catch (SQLException e) {
            e.printStackTrace();
            if (conn != null) {
                try {
                    conn.rollback(); // 回滚事务
                } catch (SQLException ex) {
                    ex.printStackTrace();
                }
            }
            return null; // 返回null表示失败
        } finally {
            DatabaseUtil.close(conn);
        }
    }

    /**
     * 取消销售订单
     * 
     * @param orderId 订单ID
     * @return 是否取消成功
     */
    public boolean cancelSalesOrder(int orderId) {
        String sql = "UPDATE sales_orders SET order_status = 'CANCELLED' WHERE id = ? AND order_status = 'PENDING'";

        Connection conn = null;
        PreparedStatement ps = null;

        try {
            conn = DatabaseUtil.getConnection();
            ps = conn.prepareStatement(sql);
            ps.setInt(1, orderId);

            return ps.executeUpdate() > 0;
        } catch (SQLException e) {
            e.printStackTrace();
            return false;
        } finally {
            DatabaseUtil.close(conn, ps);
        }
    }

    /**
     * 获取今日销售总金额
     * 
     * @param cashierId 收银员ID（null表示所有收银员）
     * @return 今日销售总金额
     */
    public BigDecimal getTodaySales(Integer cashierId) {
        StringBuilder sql = new StringBuilder(
                "SELECT COALESCE(SUM(final_amount), 0) as total_sales " +
                        "FROM sales_orders " +
                        "WHERE order_date = CURDATE() AND order_status = 'COMPLETED'");

        if (cashierId != null) {
            sql.append(" AND cashier_id = ?");
        }

        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;

        try {
            conn = DatabaseUtil.getConnection();
            ps = conn.prepareStatement(sql.toString());
            if (cashierId != null) {
                ps.setInt(1, cashierId);
            }

            rs = ps.executeQuery();

            if (rs.next()) {
                return rs.getBigDecimal("total_sales");
            }

        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DatabaseUtil.close(conn, ps, rs);
        }

        return BigDecimal.ZERO;
    }
}
